﻿using System;
using System.Collections.Generic;
using System.IO.Packaging;
using System.Linq;
using System.Text.RegularExpressions;
using System.Xml.Linq;

namespace Kivensoft.XlsReader {
	public class Sheet {
		private const string NS_MAIN = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";

		private IList<Row> rows = new List<Row>();
		private IList<Column> columns = new List<Column>();

		public Sheet(string name) { Name = name; }

		public Sheet(string name, string id, bool hidden) {
			Name = name;
			Id = id;
			Hidden = hidden;
		}

		public string Name { get; set; }

		public string Id { get; set; }

		public string Path { get; set; }

		public bool Hidden { get; set; }

		public Workbook Workbook { get; set; }

		public Row Row(int index) {
			return (index >= rows.Count) ? null : rows[index];
		}

		public Column Column(int index) {
			return (index >= columns.Count) ? null : columns[index];
		}

		public Cell Cell(int rowIndex, int columnIndex) {
			if (rowIndex < rows.Count) {
				var r = rows[rowIndex];
				return (r == null) ? null : r.Cell(columnIndex);
			}
			else if (columnIndex < columns.Count) {
				var c = columns[columnIndex];
				return (c == null) ? null : c.Cell(rowIndex);
			}
			else return null;
		}

		public Cell Cell(string name) {
			return Cell(GetRowIndex(name), GetColumnIndex(name));
		}

		public IList<Row> Rows { get { return rows; } }

		public IList<Column> Columns { get { return columns; } }

		public void Open(Package archive) {
			var entry = archive.GetPart(new Uri("/" + this.Path.ToLower(), UriKind.Relative));
			XDocument document = XDocument.Load(entry.GetStream());
			XElement root = document.Root;
			XNamespace ns = NS_MAIN;

			// Find hidden columns
			List<int> hiddenColumns = GetHiddenColumns(root, ns);

			// Loop throgh rows
			XElement sheetData = root.Element(ns + "sheetData");
			foreach (XElement eRow in sheetData.Elements(ns + "row")) {
				// Skip empty rows
				if (eRow.Descendants(ns + "v").Count() == 0) continue;

				// Set row properties
				XAttribute attr1 = eRow.Attribute("r");
				XAttribute attr2 = eRow.Attribute("hidden");
				int index = int.Parse(attr1.Value) - 1;
				bool hidden = (attr2 != null && attr2.Value == "1") ? true : false;
				Row row = new Row(index, hidden);

				// Loop through cells on row
				foreach (XElement eCell in eRow.Elements(ns + "c")) {
					// Skip empty cells
					XElement xValue = eCell.Element(ns + "v");
					if (xValue == null) continue;

					// Get cell position
					string position = eCell.Attribute("r").Value;
					int columnIndex = GetColumnIndex(position);
					int rowIndex = GetRowIndex(position);

					// Get cell style
					XAttribute s = eCell.Attribute("s");
					NumberFormat format = NumberFormat.General;
					if (s != null) {
						int styleIndex = int.Parse(s.Value);
						format = (NumberFormat)Workbook.NumberFormats[styleIndex];
					}

					// Get shared string (if any)
					string sharedString = string.Empty;
					if (IsSharedString(eCell)) {
						int number = int.Parse(xValue.Value);
						Workbook.SharedStrings.TryGetValue(number, out sharedString);
					}

					// Make column
					Column column = FindColumn(columnIndex);
					if (column == null) {
						column = new Column(columnIndex);
						column.Hidden = (hiddenColumns.Contains(columnIndex)) ? true : false;
						AddColumn(column);
					}

					// Make cell
					string cellValue = (string.IsNullOrEmpty(sharedString)) ? xValue.Value : sharedString;
					Cell cell = new Cell(cellValue);
					cell.Column = column;
					cell.Row = row;
					cell.Format = format;

					/*
					switch (format) {
						case NumberFormat.Date:
							cell.Value = Utilities.ConvertDate(cell.Value, Workbook.BaseYear);
							break;
						case NumberFormat.Time:
							cell.Value = Utilities.ConvertTime(cell.Value);
							break;
						case NumberFormat.DateTime:
							cell.Value = Utilities.ConvertDateTime(cell.Value, Workbook.BaseYear);
							break;
						default:
							break;
					}
					*/

					// Add cell to row and column
					row.AddCell(cell);
					column.AddCell(cell);
				}
				AddRow(row);
			}
		}

		private bool IsSharedString(XElement cell) {
			XAttribute a = cell.Attribute("t");
			return (a != null && "".Equals(a.Value));
		}

		private List<int> GetHiddenColumns(XElement root, XNamespace ns) {
			List<int> hiddenColumns = new List<int>();
			XElement eCols = root.Element(ns + "cols");

			if (eCols != null) {
				foreach (XElement eCol in eCols.Elements(ns + "col")) {
					XAttribute aMin = eCol.Attribute("min");
					XAttribute aMax = eCol.Attribute("max");
					XAttribute aHidden = eCol.Attribute("hidden");

					int min = (aMin != null) ? int.Parse(aMin.Value) : 0;
					int max = (aMax != null) ? int.Parse(aMax.Value) : 0;
					bool hidden = (aHidden != null && aHidden.Value == "1") ? true : false;

					if (hidden == false) continue;

					for (int i = min; i <= max; i++) hiddenColumns.Add(i);
				}
			}

			return hiddenColumns;
		}

		private int GetColumnIndex(string name) {
			if (string.IsNullOrEmpty(name)) throw new Exception("cell index must be not empty!");
			int number = 0;
			int pow = 1;
			for (int i = name.Length - 1; i >= 0; i--) {
				char c = name[i];
				if (c >= '0' && c <= '9') continue;
				number += (c - 'A' + 1) * pow;
				pow *= 26;
			}

			return number - 1;
		}

		private int GetRowIndex(string name) {
			if (string.IsNullOrEmpty(name)) throw new Exception("cell index must be not empty!");
			int number = 0;
			int pow = 1;
			for (int i = name.Length - 1; i >= 0; i--) {
				char c = name[i];
				if (c < '0' || c > '9') break;
				number += (c - '0') * pow;
				pow *= 10;
			}
			return number - 1;
		}

		private Column FindColumn(int columnIndex) {
			if (columnIndex >= columns.Count) return null;
			else return columns[columnIndex];
		}

		private void AddColumn(Column column) {
			int max = columns.Count;
			int columnIndex = column.Index;
			while (columnIndex >= max++) columns.Add(null);
			column.Sheet = this;
			columns[columnIndex] = column;
		}

		private void AddRow(Row row) {
			int max = rows.Count;
			int rowIndex = row.Index;
			while (rowIndex >= max++) rows.Add(null);
			row.Sheet = this;
			rows[rowIndex] = row;
		}
	}
}
